import pandas as pd

class ExcelExtractor:

    def __init__(self,excel_path: str):
        self.excel_path = excel_path

    def load_price_table(self,sheet_name:str="头程价格") -> pd.DataFrame:
        """
        加载运输渠道价格表
        :param excel_path: 价格表excel路径
        :param sheet_name: sheet名称
        :return: DataFrame
        """
        df = pd.read_excel(
            self.excel_path,
            sheet_name=sheet_name,
            usecols="C:W",
            skiprows=6,
            engine="openpyxl",
        )

        df.columns = [
            "一带一路.铁路.35-45",
            "空运价格表.空运小百.6-9",
            "空运价格表.空运服装.6-9",
            "大小百货服装价格表.北疆快路运.大小百货.9-15",
            "大小百货服装价格表.北疆快路运.服装.12-15",
            "大小百货服装价格表.北疆快路运.玩具专线.12-15",
            "北疆快路运单一品名专线.北疆单一专线(五金).12-15",
            "北疆快路运单一品名专线.北疆二类.9-12",
            "北疆快路运单一品名专线.北疆灯具.10-13",
            "北疆快路运单一品名专线.北疆小型器械.10-13",
            "北疆快路运单一品名专线.北疆汽配.10-13",
            "北疆快路运单一品名专线.北疆鞋子专线.13-16",
            "北疆快路运单一品名专线.北疆电动&手动工具专线.12-15",
            "北疆快路运单一品名专线.北疆单品专线.10-12",
            "北疆快路运单一品名专线.小家电专线.12-15",
            "北疆快路运单一品名专线.文胸内衣专线.15-22",
            "南疆普快/南疆单一品名专线.一类.15-18",
            "南疆普快/南疆单一品名专线.二类.15-18",
            "南疆普快/南疆单一品名专线.三类.15-18",
            "南疆普快/南疆单一品名专线.大小百货.15-22",
            "南疆普快/南疆单一品名专线.南疆服装.15-22",
        ]
        return df

    def load_transportation_channel(
            self,
            sheet_name:str = "单一品名汇总"
    ):
        # 读取Excel文件
        df = pd.read_excel(
            self.excel_path,
            sheet_name=sheet_name,
        )

        # 创建映射字典
        product_category_map = {}

        # 找出所有包含"品名"的列和包含"类别"的列
        product_cols = []
        category_cols = []

        for i, col_name in enumerate(df.columns):
            if '品名' in str(col_name):
                product_cols.append(i)
            elif '类别' in str(col_name):
                category_cols.append(i)

        # 根据列索引配对
        for i, prod_idx in enumerate(product_cols):
            if i < len(category_cols):  # 确保有对应的类别列
                cat_idx = category_cols[i]

                # 提取品名和类别
                for j in range(len(df)):
                    prod_name = df.iloc[j, prod_idx]
                    category = df.iloc[j, cat_idx]

                    if pd.notna(prod_name) and pd.notna(category):
                        product_category_map[prod_name] = category

        return product_category_map

if __name__ == '__main__':
    excel_path = "/media/duzicong/code/demo/CBEC/cbec/data/立德海外仓价格表2024最新公布9.18.xlsx"
    # df = load_price_table(excel_path)
    # product_category_map = load_transportation_channel(excel_path)
    # print(df)
